/**
 * 修复节拍字段映射脚本
 * 
 * 问题：A0101150004在生产线3、5、9、10上应该显示整机节拍，但系统显示的是普通节拍
 * 解决方案：为特定物料代码和生产线组合创建节拍字段映射规则
 * 
 * 使用方法：
 * cd D:\PPH-Production-System\PPH-Production-Deploy\app\backend
 * node ../../scripts/fix-takt-field-mapping.cjs
 */

const sqlite3 = require('./node_modules/sqlite3').verbose();
const path = require('path');

// 节拍字段映射规则
const TAKT_MAPPING_RULES = [
  {
    material_code: 'A0101150004',
    line_names: ['3', '5', '9', '10'],
    use_motor_takt: true,  // true=使用整机节拍，false=使用普通节拍
    description: 'A0101150004在生产线3、5、9、10使用整机节拍'
  }
  // 可以在这里添加更多规则
];

async function fixTaktFieldMapping() {
  return new Promise((resolve, reject) => {
    const dbPath = path.join(__dirname, '../app/backend/data/pph.sqlite');
    const db = new sqlite3.Database(dbPath);
    
    console.log('🚀 开始修复节拍字段映射...');
    
    db.serialize(() => {
      // 1. 创建节拍映射规则表（如果不存在）
      db.run(`
        CREATE TABLE IF NOT EXISTS takt_mapping_rules (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          material_code TEXT NOT NULL,
          line_name TEXT NOT NULL,
          use_motor_takt INTEGER NOT NULL DEFAULT 0,
          description TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(material_code, line_name)
        )
      `, (err) => {
        if (err) {
          console.error('❌ 创建映射规则表失败:', err.message);
          return reject(err);
        }
        console.log('✅ 节拍映射规则表已准备');
      });
      
      // 2. 插入映射规则
      const insertRule = db.prepare(`
        INSERT OR REPLACE INTO takt_mapping_rules 
        (material_code, line_name, use_motor_takt, description) 
        VALUES (?, ?, ?, ?)
      `);
      
      let ruleCount = 0;
      TAKT_MAPPING_RULES.forEach(rule => {
        rule.line_names.forEach(lineName => {
          insertRule.run([
            rule.material_code,
            lineName,
            rule.use_motor_takt ? 1 : 0,
            rule.description
          ]);
          ruleCount++;
        });
      });
      
      insertRule.finalize((err) => {
        if (err) {
          console.error('❌ 插入映射规则失败:', err.message);
          return reject(err);
        }
        console.log(`✅ 已插入 ${ruleCount} 条节拍映射规则`);
      });
      
      // 3. 验证插入的规则
      db.all(`
        SELECT material_code, line_name, use_motor_takt, description 
        FROM takt_mapping_rules 
        ORDER BY material_code, line_name
      `, (err, rows) => {
        if (err) {
          console.error('❌ 查询映射规则失败:', err.message);
          return reject(err);
        }
        
        console.log('\n📋 当前节拍映射规则:');
        console.table(rows);
      });
      
      // 4. 统计需要修复的记录
      db.get(`
        SELECT COUNT(*) as count 
        FROM pph_records r
        INNER JOIN takt_mapping_rules tmr ON r.material_code = tmr.material_code AND r.line_name = tmr.line_name
        WHERE tmr.use_motor_takt = 1
      `, (err, row) => {
        if (err) {
          console.error('❌ 统计失败:', err.message);
          return reject(err);
        }
        
        console.log(`\n📊 需要应用整机节拍映射的记录数: ${row.count}`);
        
        // 5. 显示示例记录
        db.all(`
          SELECT r.material_code, r.line_name, r.record_date, 
                 r.target_takt_time as current_takt, 
                 r.target_motor_takt_time as motor_takt,
                 tmr.use_motor_takt
          FROM pph_records r
          INNER JOIN takt_mapping_rules tmr ON r.material_code = tmr.material_code AND r.line_name = tmr.line_name
          WHERE tmr.use_motor_takt = 1
          ORDER BY r.record_date DESC
          LIMIT 5
        `, (err, rows) => {
          if (err) {
            console.error('❌ 查询示例失败:', err.message);
            return reject(err);
          }
          
          console.log('\n📋 示例记录（应使用整机节拍）:');
          console.table(rows);
          
          db.close();
          resolve();
        });
      });
    });
  });
}

// 创建节拍字段智能显示函数
function createTaktDisplayFunction() {
  const functionCode = `
-- 节拍字段智能显示函数
-- 根据映射规则决定显示普通节拍还是整机节拍
CREATE OR REPLACE FUNCTION get_display_takt(
  material_code TEXT,
  line_name TEXT,
  target_takt_time REAL,
  target_motor_takt_time REAL
) RETURNS REAL AS $$
BEGIN
  -- 检查是否有映射规则要求使用整机节拍
  IF EXISTS (
    SELECT 1 FROM takt_mapping_rules 
    WHERE takt_mapping_rules.material_code = $1 
      AND takt_mapping_rules.line_name = $2 
      AND use_motor_takt = 1
  ) THEN
    RETURN target_motor_takt_time;
  ELSE
    RETURN target_takt_time;
  END IF;
END;
$$ LANGUAGE plpgsql;
  `;
  
  console.log('\n📝 SQLite不支持自定义函数，需要在应用层处理节拍字段显示逻辑');
  console.log('建议在报表查询中使用CASE WHEN语句根据映射规则选择节拍字段');
}

// 生成修复后的报表查询SQL
function generateFixedReportSQL() {
  const fixedSQL = `
-- 修复后的报表查询SQL（节拍字段智能显示）
SELECT 
  -- 其他字段...
  
  -- 智能节拍字段：根据映射规则选择显示普通节拍或整机节拍
  CASE 
    WHEN EXISTS (
      SELECT 1 FROM takt_mapping_rules tmr 
      WHERE tmr.material_code = r.material_code 
        AND tmr.line_name = r.line_name 
        AND tmr.use_motor_takt = 1
    ) THEN (
      SELECT target_motor_takt_time FROM pph_records r_m
      WHERE r_m.record_date = r.record_date AND r_m.line_name = r.line_name
        AND r_m.order_number = r.order_number AND r_m.material_code = r.material_code
        AND r_m.target_motor_takt_time IS NOT NULL AND r_m.target_motor_takt_time > 0
      LIMIT 1
    )
    ELSE (
      SELECT target_takt_time FROM pph_records r_t
      WHERE r_t.record_date = r.record_date AND r_t.line_name = r.line_name
        AND r_t.order_number = r.order_number AND r_t.material_code = r.material_code
        AND r_t.target_takt_time IS NOT NULL AND r_t.target_takt_time > 0
      LIMIT 1
    )
  END as display_takt_time,
  
  -- 保留原始字段用于详细显示
  (SELECT target_takt_time FROM pph_records r_t
    WHERE r_t.record_date = r.record_date AND r_t.line_name = r.line_name
      AND r_t.order_number = r.order_number AND r_t.material_code = r.material_code
      AND r_t.target_takt_time IS NOT NULL AND r_t.target_takt_time > 0
    LIMIT 1) as target_takt_time,
  (SELECT target_motor_takt_time FROM pph_records r_m
    WHERE r_m.record_date = r.record_date AND r_m.line_name = r.line_name
      AND r_m.order_number = r.order_number AND r_m.material_code = r.material_code
      AND r_m.target_motor_takt_time IS NOT NULL AND r_m.target_motor_takt_time > 0
    LIMIT 1) as target_motor_takt_time

FROM pph_records r
-- WHERE 条件...
GROUP BY r.record_date, r.line_name, r.order_number, r.material_code
ORDER BY r.record_date DESC;
  `;
  
  console.log('\n📝 修复后的报表查询SQL已生成');
  console.log('需要更新 PPH-Production-Deploy/app/backend/src/routes/reports.cjs 中的查询语句');
  
  return fixedSQL;
}

// 主函数
async function main() {
  try {
    console.log('🔧 节拍字段映射修复工具');
    console.log('=====================================');
    
    await fixTaktFieldMapping();
    
    console.log('\n🎯 下一步操作建议:');
    console.log('1. 更新报表查询SQL以使用智能节拍字段显示');
    console.log('2. 在前端添加节拍字段说明（普通节拍 vs 整机节拍）');
    console.log('3. 测试A0101150004在生产线3、5、9、10的节拍显示');
    
    generateFixedReportSQL();
    
    console.log('\n✅ 节拍字段映射修复完成！');
    
  } catch (error) {
    console.error('❌ 修复失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  main();
}

module.exports = {
  fixTaktFieldMapping,
  TAKT_MAPPING_RULES
};
